﻿CREATE PROCEDURE [dbo].[proc_Finance_Outlay_Getlist]
	(
		@CompanyId VARCHAR(500),
		@DeptId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@sort varchar(100),
		@PaymentTypeId int,
		@BankId int,
		@CateOneId int,
		@CateTwoId int,
		@SubjectId VARCHAR(1000),
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex INT,
		@checkxj bit,
		@rblbank INT
	)
AS
BEGIN
	
DECLARE @TempStr VARCHAR(500),@StrSql NVARCHAR(4000),@bmbh varchar(30)
SET @TempStr=''
select  @bmbh=bmbh from department where id=@DeptId
IF @CompanyId <>''
BEGIN
	SET @TempStr=@TempStr+' R.CompanyIdCur in ('+@CompanyId+') and '
END

IF @DeptId<>0 
Begin
	set @TempStr=@TempStr+' R.DepartmentIdCur in (select id from department where left(bmbh,'+Convert(varchar,len(@bmbh))+')='+@bmbh+' ) and '
End

IF @PaymentTypeId=1 --现金
BEGIN   --查现金时，还需要查询电汇中需要算在现金中的电汇账号
--SET @TempStr='(R.PaymentTypeId=1 
--                  or r.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid='+CONVERT(VARCHAR, @CompanyId)+')) and '
IF @checkxj=0
BEGIN
	SET @TempStr=@TempStr+' R.PaymentTypeId=1  and '
END
ELSE 
	BEGIN
		SET @TempStr=@TempStr+'(R.PaymentTypeId=1 
                  or r.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid='+CONVERT(VARCHAR, @CompanyId)+')) and '
	END
END
ELSE IF @PaymentTypeId=2 --银行
BEGIN
	IF  @BankId=0 --电汇账号选择为全部时，查询所有算在银行中的电汇金额
	BEGIN
		SET @TempStr=@TempStr+'r.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+' and Companyid='+CONVERT(VARCHAR, @CompanyId)+') and '
	END
	ELSE
		BEGIN--选择了电汇账号时，查询该账号下的金额 
			SET @TempStr=@TempStr+'r.BankId =(select id from Finance_Bank where id='+CONVERT(VARCHAR, @BankId)+' and  PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+') and '
		END
END
ELSE IF @PaymentTypeId=3 --支票
BEGIN
	SET @TempStr=@TempStr+'(R.PaymentTypeId=3 ) and '
END
ELSE
	BEGIN
		set @TempStr=@TempStr+' R.PaymentTypeId in(1,2,3)  and '
	END

if @CateOneId<>0
begin
	SET @TempStr=@TempStr+'R.CateOneId='+CONVERT(varchar,@CateOneId)+' and '
end	

if @CateTwoId<>0
begin 
	SET @TempStr=@TempStr+'CateTwoId='+CONVERT(varchar,@CateTwoId)+' and '
end

if @SubjectId<>''
begin 
	SET @TempStr=@TempStr+' R.SubjectId in ('+@SubjectId+') and '
end


IF @Stext <>''
BEGIN
	SET @TempStr=@TempStr+'R.Summary like ''%'+ @Stext +'%'' AND '
END

If @sort =''
Begin
	Set @sort='R.CreateDate Desc'
End	
	
SET @StrSql='
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY '+@sort+')AS Row,
		R.Id,
		R.CreateDate,
		R.TradeDate,
		R.Summary,
		R.SubjectName,
		R.PaymentTypeName,
		R.BankName,
		R.OutlayPrice,
		R.AgentName,
		R.EName,
		R.Description,
		R.OutlayReason,
		(Select CompanyName From Company Where Id=R.CompanyIdCur) As CompanyName,
		(Select DName From Department Where Id=R.DepartmentIdCur) As DepCurName
	From Finance_Outlay R
	Where '+@TempStr+' R.TradeDate between '''+@StartDate+''' and '''+@EndDate+'''
	
	)
	
	Select * From list,(Select Count(Id) as cid,ISNULL(Sum(OutlayPrice),0) as tsum  FROM list) as ab Where Row between '+CONVERT(varchar,@StartIndex)+' and '+CONVERT(varchar,@EndIndex)+'
	'
	
	EXEC(@StrSql)
  PRINT(@StrSql)
end
	RETURN
